﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using DHQGHN.PM2.Models;
using DHQGHN.PM2.ViewModels;
using System.Net;
using System.Configuration;
using System.Text;
using Newtonsoft.Json;
using System.Runtime.Serialization;
using System.IO;
using DHQGHN.PM2.Controllers;
using System.Runtime.Serialization.Json;
using System.Collections;
using System.Data;
using System.ComponentModel;

using System.Reflection;
using System.Runtime.InteropServices;
using ICSharpCode.SharpZipLib.Zip;

using System.Diagnostics;
using System.Drawing;
using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Style;
using ICSharpCode.SharpZipLib.Core;

namespace DHQGHN.PM2.Controllers
{

    [Authorize]
    public class AreaController : BaseController
    {
        log4net.ILog logger = log4net.LogManager.GetLogger(typeof(AccountController));
        public ActionResult Index()
        {
            return View();
        }
        #region Bản đồ
        public ActionResult getPrint()
        {
            return View();
        }

        public ActionResult getPrint2()
        {
            return View();
        }

        public ActionResult getPrintA4()
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[103] = "active";
            ViewBag.ActiveMenus[111] = "active";
            try
            {
                ViewBag.Message = "Thống kê khu vực hành chính";
                ViewBag.Path = "/Area/ManageArea";

                string strAdminCode = MyUser.AdminCode; //"1008202749";
                string strCommuneCode = strAdminCode.Substring(5, 5);
                string UserName = MyUser.UserName;
                List<AreaModel> areaList = new List<AreaModel>();
                using (WebClient webClient = new WebClient())
                {
                    Helper.SetHeaderInfo(webClient, "GetAllUsername");
                    webClient.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();
                    try
                    {
                        string dwml = webClient.DownloadString(areaServiceUrl + "GetAllUsername/" + UserName);
                        areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(dwml).Result;
                    }
                    catch (WebException wex)
                    {
                        logger.Error(wex.ToString());
                        throw;
                    }
                    ViewData["LIST_AREA"] = lstItems;

                    //phunx1, 20150514: pass url Service len view
                    ViewBag.ServiceUrl = serviceUrl;
                    ViewBag.CommuneCode = strCommuneCode;
                }

                ViewBag.AreaList = areaList;
                return View();
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        public ActionResult getPrintA4TienDo()
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[106] = "active";
            ViewBag.ActiveMenus[151] = "active";
            try
            {
                ViewBag.Message = "Thống kê khu vực hành chính";
                ViewBag.Path = "/Area/ManageArea";

                string strAdminCode = MyUser.AdminCode; //"1008202749";
                string strCommuneCode = strAdminCode.Substring(5, 5);
                string UserName = MyUser.UserName;
                List<AreaModel> areaList = new List<AreaModel>();
                using (WebClient webClient = new WebClient())
                {
                    Helper.SetHeaderInfo(webClient, "GetAllUsername");
                    webClient.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();
                    try
                    {
                        string dwml = webClient.DownloadString(areaServiceUrl + "GetAllUsername/" + UserName);
                        areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(dwml).Result;
                    }
                    catch (WebException wex)
                    {
                        logger.Error(wex.ToString());
                        throw;
                    }
                    ViewData["LIST_AREA"] = lstItems;

                    //phunx1, 20150514: pass url Service len view
                    ViewBag.ServiceUrl = serviceUrl;
                    ViewBag.CommuneCode = strCommuneCode;
                }

                ViewBag.AreaList = areaList;
                return View();
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }
        }

        public ActionResult getPrintA3TienDo()
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[106] = "active";
            ViewBag.ActiveMenus[151] = "active";
            try
            {
                ViewBag.Message = "Thống kê khu vực hành chính";
                ViewBag.Path = "/Area/ManageArea";

                string strAdminCode = MyUser.AdminCode; //"1008202749";
                string strCommuneCode = strAdminCode.Substring(5, 5);
                string UserName = MyUser.UserName;
                List<AreaModel> areaList = new List<AreaModel>();
                using (WebClient webClient = new WebClient())
                {
                    Helper.SetHeaderInfo(webClient, "GetAllUsername");
                    webClient.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();
                    try
                    {
                        string dwml = webClient.DownloadString(areaServiceUrl + "GetAllUsername/" + UserName);
                        areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(dwml).Result;
                    }
                    catch (WebException wex)
                    {
                        logger.Error(wex.ToString());
                        throw;
                    }
                    ViewData["LIST_AREA"] = lstItems;

                    //phunx1, 20150514: pass url Service len view
                    ViewBag.ServiceUrl = serviceUrl;
                    ViewBag.CommuneCode = strCommuneCode;
                }

                ViewBag.AreaList = areaList;
                return View();
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }
        }

        public ActionResult getPrintA3()
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[103] = "active";
            ViewBag.ActiveMenus[111] = "active";
            try
            {
                ViewBag.Message = "Thống kê khu vực hành chính";
                ViewBag.Path = "/Area/ManageArea";

                string strAdminCode = MyUser.AdminCode; //"1008202749";
                string strCommuneCode = strAdminCode.Substring(5, 5);
                string UserName = MyUser.UserName;
                List<AreaModel> areaList = new List<AreaModel>();
                using (WebClient webClient = new WebClient())
                {
                    Helper.SetHeaderInfo(webClient, "GetAllUsername");
                    webClient.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();
                    try
                    {
                        string dwml = webClient.DownloadString(areaServiceUrl + "GetAllUsername/" + UserName);
                        areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(dwml).Result;
                    }
                    catch (WebException wex)
                    {
                        logger.Error(wex.ToString());
                        throw;
                    }
                    ViewData["LIST_AREA"] = lstItems;

                    //phunx1, 20150514: pass url Service len view
                    ViewBag.ServiceUrl = serviceUrl;
                    ViewBag.CommuneCode = strCommuneCode;
                }

                ViewBag.AreaList = areaList;
                return View();
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }
        }

        public ActionResult getViewMap()
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[102] = "active";
            ViewBag.ActiveMenus[107] = "active";
            try
            {
                ViewBag.Message = "Quản lý địa bàn điều tra";
                ViewBag.Path = "/Area/IndexArea";

                string strAdminCode = MyUser.AdminCode; //"1008202749";
                string strCommuneCode = strAdminCode.Substring(5, 5);
                string UserName = MyUser.UserName;
                List<AreaModel> areaList = new List<AreaModel>();
                using (WebClient webClient = new WebClient())
                {
                    Helper.SetHeaderInfo(webClient, "GetAllUsername");
                    webClient.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();
                    try
                    {
                        string dwml = webClient.DownloadString(areaServiceUrl + "GetAllUsername/" + UserName);
                        areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(dwml).Result;
                    }
                    catch (WebException wex)
                    {
                        logger.Error(wex.ToString());
                        throw;
                    }
                    ViewData["LIST_AREA"] = lstItems;

                    //phunx1, 20150514: pass url Service len view
                    ViewBag.ServiceUrl = serviceUrl;
                    ViewBag.CommuneCode = strCommuneCode;
                }

                ViewBag.AreaList = areaList;
                return View();
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }
        }


        [AjaxValidateAntiForgeryToken]
        public JsonResult GetAllAreaCodeName(string area_code)
        {
            try
            {
                //ViewBag.Message = "Thống kê khu vực hành chính";
                //ViewBag.Path = "/Area/ManageArea";

                string strAdminCode = MyUser.AdminCode; //"1008202749";
                string strCommuneCode = strAdminCode.Substring(5, 5);
                string UserName = MyUser.UserName;
                List<AreaModel> areaList = new List<AreaModel>();
                using (WebClient webClient = new WebClient())
                {
                    Helper.SetHeaderInfo(webClient, "GetAllUsername");
                    webClient.Encoding = Encoding.UTF8;
                    string dwml = webClient.DownloadString(areaServiceUrl + "GetAllUsername/" + UserName);
                    areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(dwml).Result;
                    ViewBag.ServiceUrl = serviceUrl;
                    ViewBag.CommuneCode = strCommuneCode;
                }
                string areaName = string.Empty;
                string areaId = string.Empty;
                foreach (var h in areaList)
                {
                    if (area_code == h.AREA_CODE)
                    {
                        areaName = h.AREA_NAME;
                        areaId = h.AREA_ID;
                    }
                }

                string xaName = string.Empty;
                string huyenName = string.Empty;
                string tinhName = string.Empty;
                int soho = 0;
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetByID");
                    Helper.SetHeaderInfo(wc, "GetByAreaID");
                    wc.Encoding = Encoding.UTF8;
                    //Lấy thông tin xã phường
                    AdminisUnitModel UnitModel = new AdminisUnitModel();
                    string url, content;
                    try
                    {
                        url = adminUnitServiceUrl + string.Format("GetByID/{0}", MyUser.AdminID);
                        content = wc.DownloadString(url);
                        UnitModel = JsonConvert.DeserializeObjectAsync<AdminisUnitModel>(content).Result;
                        if (UnitModel == null) UnitModel = new AdminisUnitModel();
                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex);
                        throw;
                    }
                    xaName = UnitModel.ADM_NAME;

                    //Lấy thông tin huyện/thị xã
                    try
                    {
                        url = adminUnitServiceUrl + string.Format("GetByID/{0}", UnitModel.P_ID);
                        content = wc.DownloadString(url);
                        UnitModel = JsonConvert.DeserializeObjectAsync<AdminisUnitModel>(content).Result;
                        if (UnitModel == null) UnitModel = new AdminisUnitModel();
                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex);
                        throw;
                    }
                    huyenName = UnitModel.ADM_NAME;

                    //Lấy thông tin tỉnh/thành phố
                    try
                    {
                        url = adminUnitServiceUrl + string.Format("GetByID/{0}", UnitModel.P_ID);
                        content = wc.DownloadString(url);
                        UnitModel = JsonConvert.DeserializeObjectAsync<AdminisUnitModel>(content).Result;
                        if (UnitModel == null) UnitModel = new AdminisUnitModel();
                    }
                    catch { }
                    tinhName = UnitModel.ADM_NAME;

                    url = houseHoldServiceUrl + string.Format("GetByAreaID/{0}", areaId);
                    content = wc.DownloadString(url);
                    List<Hashtable> houseHoldList = JsonConvert.DeserializeObjectAsync<List<Hashtable>>(content).Result;
                    soho = houseHoldList.Count;

                }
                return Json(new { areaName, xaName, huyenName, tinhName, soho }, JsonRequestBehavior.AllowGet);
                //return Json(new, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }
        }

        //Update tọa độ hộ dân cư
        [AjaxValidateAntiForgeryToken]
        public JsonResult EditHDC(string Stringlaglong)
        {
            try
            {
                EditHoDanCu HDC = new EditHoDanCu();

                HDC.STRINGLAGLONG = Stringlaglong;
                using (WebClient wc = new WebClient())
                {
                    if (ModelState.IsValid)
                    {
                        using (MemoryStream ms = new MemoryStream())
                        {
                            Helper.SetHeaderInfo(wc, "EditHDC");
                            DataContractJsonSerializer serializerToUpload = new DataContractJsonSerializer(typeof(EditHoDanCu));
                            serializerToUpload.WriteObject(ms, HDC);
                            wc.Headers["Content-type"] = "application/json; charset=utf-8";
                            wc.UploadData(houseHoldServiceUrl + "EditHDC", "POST", ms.ToArray());

                            LogStream.Write<EditHoDanCu>(MyUser.UserName,
                                CommonData.AppName,
                                GetFunction(),
                                "Cập nhật tọa độ hộ dân cư - EditHDCs", HDC);
                        }
                    }
                    return Json(new { Result = String.Format("Cập nhật thành công.") });
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }
        }

        //Edit markers -- chỉnh sửa tọa độ hộ dân cư.
        public ActionResult EditMarkers()
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[103] = "active";
            ViewBag.ActiveMenus[239] = "active";
            try
            {
                ViewBag.Message = "Thống kê khu vực hành chính";
                ViewBag.Path = "/Area/EditMarkers";

                string strAdminCode = MyUser.AdminCode; //"1008202749";
                string strCommuneCode = strAdminCode.Substring(5, 5);
                string UserName = MyUser.UserName;
                List<AreaModel> areaList = new List<AreaModel>();
                using (WebClient webClient = new WebClient())
                {
                    //string serviceUrl = ConfigurationManager.AppSettings["WcfServiceUrl"].ToString();
                    //string url = areServiceUrl;

                    //string serviceMethod = "AreaService.svc/GetAllUsername/" + strAdminCode;
                    Helper.SetHeaderInfo(webClient, "GetAllUsername");
                    webClient.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();
                    try
                    {
                        string dwml = webClient.DownloadString(areaServiceUrl + "GetAllUsername/" + UserName);
                        areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(dwml).Result;
                    }
                    catch (WebException wex)
                    {
                        logger.Error(wex.ToString());
                        throw;
                    }
                    foreach (var area in areaList)
                    {
                        if (area != null)
                            lstItems.Add(new SelectListItem
                            {
                                Value = area.AREA_CODE,
                                Text = area.AREA_NAME
                            });
                    }
                    ViewData["LIST_AREA"] = lstItems;

                    //phunx1, 20150514: pass url Service len view
                    ViewBag.ServiceUrl = serviceUrl;
                    ViewBag.CommuneCode = strCommuneCode;
                }

                ViewBag.AreaList = areaList;
                return View();
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }

        }

        //Thống kê mật độ dân số
        public ActionResult ThongkeMatDoDanSo()
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[106] = "active";
            ViewBag.ActiveMenus[228] = "active";
            try
            {
                ViewBag.Message = "Thống kê mật độ dân số";
                ViewBag.Path = "/Area/ThongkeMatDoDanSo";

                string strAdminCode = MyUser.AdminCode; //"1008202749";
                string strCommuneCode = string.Empty;
                if (strAdminCode.Count() == 10)
                {
                    strCommuneCode = strAdminCode.Substring(5, 5);
                }
                else if (strAdminCode.Count() == 5)
                {
                    strCommuneCode = strAdminCode.Substring(2, 3);
                }
                else
                {
                    strCommuneCode = strAdminCode;
                }
                string UserName = MyUser.UserName;
                List<AreaModel> areaList = new List<AreaModel>();
                using (WebClient webClient = new WebClient())
                {
                    //string serviceUrl = ConfigurationManager.AppSettings["WcfServiceUrl"].ToString();
                    //string url = areServiceUrl;

                    //string serviceMethod = "AreaService.svc/GetAllUsername/" + strAdminCode;
                    Helper.SetHeaderInfo(webClient, "GetAllUsername");
                    webClient.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();
                    try
                    {
                        string dwml = webClient.DownloadString(areaServiceUrl + "GetAllUsername/" + UserName);
                        areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(dwml).Result;
                    }
                    catch (WebException wex)
                    {
                        logger.Error(wex.ToString());
                        throw;
                    }
                    foreach (var area in areaList)
                    {
                        if (area != null)
                            lstItems.Add(new SelectListItem
                            {
                                Value = area.AREA_CODE,
                                Text = area.AREA_NAME
                            });
                    }
                    ViewData["LIST_AREA"] = lstItems;

                    //phunx1, 20150514: pass url Service len view
                    ViewBag.ServiceUrl = serviceUrl;
                    ViewBag.CommuneCode = strCommuneCode;
                }

                ViewBag.AreaList = areaList;
                return View();
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }
        }

        //Lấy mã TP - quận - huyện - xã
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetUnixDPC(string ma_quan, string ma_huyen, string ma_tinh, string dk, string startdate, string enddate)
        {
            try
            {
                ModelAreaListThongKe ThongKe = new ModelAreaListThongKe();
                List<ThongKeDS> ThongkeDSList = new List<ThongKeDS>();
                if (String.IsNullOrEmpty(ma_quan))
                    ma_quan = null;

                if (String.IsNullOrEmpty(ma_huyen))
                    ma_huyen = null;

                if (String.IsNullOrEmpty(ma_tinh))
                    ma_tinh = null;

                if (String.IsNullOrEmpty(startdate) || startdate == "dd/mm/yyyy")
                    ThongKe.STARTDATE = null;
                else
                    ThongKe.STARTDATE = startdate;

                if (String.IsNullOrEmpty(enddate) || enddate == "dd/mm/yyyy")
                    ThongKe.ENDDATE = null;
                else
                    ThongKe.ENDDATE = enddate;

                ThongKe.PROVINCE_CODE = ma_quan;
                ThongKe.DISTRICT_CODE = ma_huyen;
                ThongKe.COMMUNE_CODE = ma_tinh;


                ThongKe.dk = dk;
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetByCode");
                    wc.Encoding = Encoding.UTF8;
                    wc.Headers["Content-type"] = "application/json; charset=utf-8";
                    var jsonString = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(ThongKe);
                    string ThongKeDS = wc.UploadString(adminUnitServiceUrl + "GetByCode", "POST", jsonString);

                    ThongkeDSList = JsonConvert.DeserializeObjectAsync<List<ThongKeDS>>(ThongKeDS).Result;
                }
                return Json(ThongkeDSList, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }
        }

        //thống kê khu vực hành chính theo tiến độ.
        public ActionResult QlyDiaBanTheoTienDo()
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[106] = "active";
            ViewBag.ActiveMenus[209] = "active";
            try
            {
                ViewBag.Message = "Bản đồ tiến độ điều tra";
                ViewBag.Path = "/Area/QLyDiaBanTheoTienDo";

                string strAdminCode = MyUser.AdminCode; //"1008202749";
                string strCommuneCode = strAdminCode.Substring(5, 5);
                List<AreaModel> areaList = new List<AreaModel>();
                using (WebClient webClient = new WebClient())
                {
                    //string serviceUrl = ConfigurationManager.AppSettings["WcfServiceUrl"].ToString();
                    //string url = areServiceUrl;

                    //string serviceMethod = "AreaService.svc/GetAllUsername/" + strAdminCode;
                    Helper.SetHeaderInfo(webClient, "GetByADMUnitCode");
                    webClient.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();
                    try
                    {
                        string dwml = webClient.DownloadString(areaServiceUrl + "GetByADMUnitCode/" + MyUser.AdminCode);
                        areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(dwml).Result;
                    }
                    catch (WebException wex)
                    {
                        throw;
                    }
                    foreach (var area in areaList)
                    {
                        if (area != null)
                            lstItems.Add(new SelectListItem
                            {
                                Value = area.AREA_CODE,
                                Text = area.AREA_NAME
                            });
                    }
                    ViewData["LIST_AREA"] = lstItems;

                    //phunx1, 20150514: pass url Service len view
                    ViewBag.ServiceUrl = serviceUrl;
                    ViewBag.CommuneCode = strCommuneCode;
                }

                ViewBag.AreaList = areaList;
                return View();
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }

        }

        //Thống kê khu vực hành chính
        public ActionResult ManageArea()
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[103] = "active";
            ViewBag.ActiveMenus[111] = "active";

            try
            {
                ViewBag.Message = "Thống kê khu vực hành chính";
                ViewBag.Path = "/Area/ManageArea";

                string strAdminCode = MyUser.AdminCode; //"1008202749";
                string strCommuneCode = strAdminCode.Substring(5, 5);
                string UserName = MyUser.UserName;
                List<AreaModel> areaList = new List<AreaModel>();
                using (WebClient webClient = new WebClient())
                {
                    //string serviceUrl = ConfigurationManager.AppSettings["WcfServiceUrl"].ToString();
                    //string url = areServiceUrl;

                    //string serviceMethod = "AreaService.svc/GetAllUsername/" + strAdminCode;
                    Helper.SetHeaderInfo(webClient, "GetAllUsername");
                    webClient.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();
                    try
                    {
                        string dwml = webClient.DownloadString(areaServiceUrl + "GetAllUsername/" + UserName);
                        areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(dwml).Result;
                    }
                    catch (WebException wex)
                    {
                        logger.Error(wex.ToString());
                        throw;
                    }
                    foreach (var area in areaList)
                    {
                        if (area != null)
                            lstItems.Add(new SelectListItem
                            {
                                Value = area.AREA_CODE,
                                Text = area.AREA_NAME
                            });
                    }
                    ViewData["LIST_AREA"] = lstItems;

                    //phunx1, 20150514: pass url Service len view
                    ViewBag.ServiceUrl = serviceUrl;
                    ViewBag.CommuneCode = strCommuneCode;
                }

                ViewBag.AreaList = areaList;
                return View();
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }

        }

        //Lấy tất cả household address của một hộ dân cư trong địa bàn
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetAllHouseID(string AREA_CODE)
        {
            try
            {
                List<HouseHoldModel> householdList = new List<HouseHoldModel>();
                List<BangKeViewModel> household = new List<BangKeViewModel>();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetAllByEACode");
                    wc.Encoding = Encoding.UTF8;
                    string householdid = wc.DownloadString(houseHoldServiceUrl + "GetAllByEACode" + "/" + AREA_CODE);
                    householdList = JsonConvert.DeserializeObjectAsync<List<HouseHoldModel>>(householdid).Result;

                    string household_id = string.Empty;
                    string householdDownload = string.Empty;

                    using (WebClient wc1 = new WebClient())
                    {
                        Helper.SetHeaderInfo(wc1, "GetHouseHoldAddressByID");
                        foreach (var h in householdList)
                        {
                            household_id = h.HOUSEHOLD_ID;
                            householdDownload = wc1.DownloadString(houseHoldServiceUrl + "GetHouseHoldAddressByID" + "/" + household_id);
                            household.Add(JsonConvert.DeserializeObjectAsync<BangKeViewModel>(householdDownload).Result);
                        }
                    }
                }
                return Json(household, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }
        }

        //gopro, 20150424: ve so do dia ban dieu tra
        public ActionResult DrawArea()
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[103] = "active";
            ViewBag.ActiveMenus[125] = "active";
            try
            {
                ViewBag.Message = "Ve so do ";
                ViewBag.Path = "/Area/DrawArea";

                string strAdminCode = MyUser.AdminCode; //"1008202749";
                string strCommuneCode = strAdminCode.Substring(5, 5);
                string UserName = MyUser.UserName;
                List<AreaModel> areaList = new List<AreaModel>();
                using (WebClient webClient = new WebClient())
                {
                    //string serviceUrl = ConfigurationManager.AppSettings["WcfServiceUrl"].ToString();
                    //string url = areServiceUrl;

                    //string serviceMethod = "AreaService.svc/GetAllUsername/" + strAdminCode;
                    webClient.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();
                    Helper.SetHeaderInfo(webClient, "GetAllUsername");
                    try
                    {
                        string dwml = webClient.DownloadString(areaServiceUrl + "GetAllUsername/" + UserName);
                        areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(dwml).Result;
                    }
                    catch (WebException wex)
                    {
                        logger.Error(wex.ToString());
                        throw;
                    }
                    foreach (var area in areaList)
                    {
                        if (area != null)
                            lstItems.Add(new SelectListItem
                            {
                                Value = area.AREA_CODE,
                                Text = area.AREA_NAME
                            });
                    }
                    ViewData["LIST_AREA"] = lstItems;

                    //phunx1, 20150514: pass url Service len view
                    ViewBag.ServiceUrl = serviceUrl;
                    ViewBag.CommuneCode = strCommuneCode;
                }

                ViewBag.AreaList = areaList;
                return View();
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }

        }

        //Tạo thêm thuộc tính vẽ mới địa bàn
        public ActionResult CreateNewAreaMap()
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[103] = "active";
            ViewBag.ActiveMenus[110] = "active";
            try
            {
                ViewBag.Message = "Ve so do ";
                ViewBag.Path = "/Area/CreateNewAreaMap";
                string strAdminCode = MyUser.AdminCode;
                string strCommuneCode = strAdminCode.Substring(5, 5); //"1008202749";
                string UserName = MyUser.UserName;
                List<AreaModel> areaList = new List<AreaModel>();
                using (WebClient webClient = new WebClient())
                {
                    //string serviceUrl = ConfigurationManager.AppSettings["WcfServiceUrl"].ToString();
                    //string url = areServiceUrl;

                    //string serviceMethod = "AreaService.svc/GetAllUsername/" + strAdminCode;
                    Helper.SetHeaderInfo(webClient, "GetAllUsername");
                    webClient.Encoding = Encoding.UTF8;
                    List<SelectListItem> lstItems = new List<SelectListItem>();
                    try
                    {
                        string dwml = webClient.DownloadString(areaServiceUrl + "GetAllUsername/" + UserName);
                        areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(dwml).Result;
                    }
                    catch (WebException wex)
                    {
                        throw;
                    }
                    foreach (var area in areaList)
                    {
                        if (area != null)
                            lstItems.Add(new SelectListItem
                            {
                                Value = area.AREA_CODE,
                                Text = area.AREA_NAME
                            });
                    }
                    ViewData["LIST_AREA"] = lstItems;

                    //phunx1, 20150514: pass url Service len view
                    ViewBag.ServiceUrl = serviceUrl;
                    ViewBag.CommuneCode = strCommuneCode;
                }

                ViewBag.AreaList = areaList;
                return View();
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }

        }


        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        //gopro, 20150514: ham luu toa do ranh gioi dia ban dieu tra
        public JsonResult AddAreaBoundPoint(List<string> values)
        {
            //string areaID = values[0];
            string listPoints = string.Empty;
            if (values != null)
            {
                for (int i = 0; i < values.Count; i++)
                {
                    listPoints += values[i];
                    if (i < values.Count - 1) listPoints += "-";
                }
            }
            try
            {
                using (WebClient wc = new WebClient())
                {
                    if (ModelState.IsValid)
                    {
                        using (MemoryStream ms = new MemoryStream())
                        {
                            Helper.SetHeaderInfo(wc, "AddAreaBoundPoint");
                            //DataContractJsonSerializer serializerToUpload = new DataContractJsonSerializer(typeof(AreaBoundPointModel));
                            DataContractJsonSerializer serializerToUpload = new DataContractJsonSerializer(typeof(string));
                            serializerToUpload.WriteObject(ms, listPoints);

                            wc.Headers["Content-type"] = "application/json; charset=utf-8";
                            wc.UploadData(areaServiceUrl + "AddAreaBoundPoint", "POST", ms.ToArray());

                            LogStream.Write(MyUser.UserName,
                            CommonData.AppName,
                            GetFunction(),
                            listPoints,
                            "Lưu tọa độ ranh giới địa bàn điều tra - AddAreaBoundPoint");
                        }
                    }
                    return Json(new { Result = String.Format("Fist item in list: '{0}'", values[0]) });
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }

            //return Json(new { Result = String.Format("Fist item in list: '{0}'", values[0]) });
        }

        //ham get ra ranh gioi toa do cua dia ban
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetAllAreaCode()
        {
            try
            {
                List<AreaBoundPointModel> GetAreaPoint = new List<AreaBoundPointModel>();

                string adminCode = MyUser.AdminCode;
                List<AreaModel> areaList = new List<AreaModel>();

                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetByAdminCode");
                    wc.Encoding = Encoding.UTF8;
                    string areaDownload = wc.DownloadString(areaServiceUrl + "GetByAdminCode" + "/" + adminCode);
                    areaList = JsonConvert.DeserializeObjectAsync<List<Models.AreaModel>>(areaDownload).Result;
                }

                var list = new List<string>();

                foreach (AreaModel h in areaList)
                {
                    list.Add(h.AREA_CODE);
                }

                return Json(list, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }
        }

        //string AREA_CODE = string.Empty;
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetAreaBoundPointByAreaCode(string AREA_CODE)
        {
            try
            {
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetAreaBoundPointByAreaCode");
                    wc.Encoding = Encoding.UTF8;
                    string getAreaPointDownload = wc.DownloadString(areaServiceUrl + "GetAreaBoundPointByAreaCode" + "/" + AREA_CODE);
                    List<AreaBoundPointModel> GetAreaPoint = JsonConvert.DeserializeObjectAsync<List<AreaBoundPointModel>>(getAreaPointDownload).Result;
                    return Json(GetAreaPoint, JsonRequestBehavior.AllowGet);
                }


            }
            catch (Exception ex)
            {
                logger.Error(ex);
                throw;
            }
        }

        #endregion

        #region popup delete area Quyvd Index
        public ActionResult ViewForDelete(string Area_ID)
        {
            try
            {
                ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
                ViewBag.ActiveMenus[102] = "active";
                ViewBag.ActiveMenus[107] = "active";


                AreaModel DiaBanDieuTra = new AreaModel();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetbyID");
                    wc.Encoding = Encoding.UTF8;
                    string DiaBanDieuTraDownload = wc.DownloadString(areaServiceUrl + "GetbyID" + "/" + Area_ID);
                    DiaBanDieuTra = JsonConvert.DeserializeObjectAsync<AreaModel>(DiaBanDieuTraDownload).Result;
                    string AdminDownload = wc.DownloadString(adminUnitServiceUrl + "GetByID" + "/" + DiaBanDieuTra.ADM_UNIT_ID);
                    AdminisUnitModel Admin = JsonConvert.DeserializeObjectAsync<AdminisUnitModel>(AdminDownload).Result;
                    ViewBag.ADM_UNIT = Admin.PROVINCE_NAME + " - " + Admin.DISTRICT_NAME + " - " + Admin.COMMUNE_NAME;

                    //List<AreaModel> items = new List<AreaModel>() { DiaBanDieuTra };

                }
                return PartialView("ViewForDeletePartail", DiaBanDieuTra);

            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        //[HttpPost, ActionName("DeleteDiaBanDT")]
        [AjaxValidateAntiForgeryToken]
        public ActionResult DeleteDiaBanDTConfirmed(string Area_ID)
        {
            try
            {
                using (WebClient wc = new WebClient())
                {
                    using (MemoryStream ms = new MemoryStream())
                    {
                        Helper.SetHeaderInfo(wc, "DeleteArea");
                        DataContractJsonSerializer serializerToUpload = new DataContractJsonSerializer(typeof(AreaModel));
                        serializerToUpload.WriteObject(ms, Area_ID);
                        wc.Headers["Content-type"] = "application/json; charset=utf-8";
                        wc.UploadData(areaServiceUrl + "DeleteArea", "POST", ms.ToArray());

                        LogStream.Write(MyUser.UserName,
                            CommonData.AppName,
                            GetFunction(),
                            CommonData.GetListId(new string[] { Area_ID }),
                            "Xóa địa bàn điều tra - DeleteDiaBanDTConfirmed");

                        return RedirectToAction("IndexArea");
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        #endregion

        #region popup view area Quyvd Index
        public ActionResult ViewForDetail(string Area_ID)
        {
            try
            {
                ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
                ViewBag.ActiveMenus[102] = "active";
                ViewBag.ActiveMenus[107] = "active";


                AreaModel DiaBanDieuTra = new AreaModel();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetbyID");
                    wc.Encoding = Encoding.UTF8;
                    string DiaBanDieuTraDownload = wc.DownloadString(areaServiceUrl + "GetbyID" + "/" + Area_ID);
                    DiaBanDieuTra = JsonConvert.DeserializeObjectAsync<AreaModel>(DiaBanDieuTraDownload).Result;
                    AdminisUnitModel Admin = new AdminisUnitModel();
                    string AdminDownload = wc.DownloadString(adminUnitServiceUrl + "GetByID" + "/" + DiaBanDieuTra.ADM_UNIT_ID);
                    Admin = JsonConvert.DeserializeObjectAsync<AdminisUnitModel>(AdminDownload).Result;
                    ViewBag.ADM_UNIT = Admin.PROVINCE_NAME + " - " + Admin.DISTRICT_NAME + " - " + Admin.COMMUNE_NAME;

                    //List<AreaModel> items = new List<AreaModel>() { DiaBanDieuTra };

                }
                return PartialView("ViewForViewPartail", DiaBanDieuTra);

            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        #endregion

        #region popup Edit area QuyVd Index
        public ActionResult ViewForEdit(string Area_ID)
        {
            try
            {
                ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
                ViewBag.ActiveMenus[102] = "active";
                ViewBag.ActiveMenus[107] = "active";


                AreaModel DiaBanDieuTra = new AreaModel();
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "GetbyID");
                    wc.Encoding = Encoding.UTF8;
                    string DiaBanDieuTraDownload = wc.DownloadString(areaServiceUrl + "GetbyID" + "/" + Area_ID);
                    DiaBanDieuTra = JsonConvert.DeserializeObjectAsync<AreaModel>(DiaBanDieuTraDownload).Result;
                    AdminisUnitModel Admin = new AdminisUnitModel();
                    string AdminDownload = wc.DownloadString(adminUnitServiceUrl + "GetByID" + "/" + DiaBanDieuTra.ADM_UNIT_ID);
                    Admin = JsonConvert.DeserializeObjectAsync<AdminisUnitModel>(AdminDownload).Result;
                    ViewBag.ADM_UNIT = Admin.PROVINCE_NAME + " - " + Admin.DISTRICT_NAME + " - " + Admin.COMMUNE_NAME;

                    //List<AreaModel> items = new List<AreaModel>() { DiaBanDieuTra };

                }
                return PartialView("ViewForEditPartail", DiaBanDieuTra);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        [HttpPost]
        //[ValidateAntiForgeryToken]
        [AjaxValidateAntiForgeryToken]
        public ActionResult SuaDiaBanDT(string AREA_ID, string AREA_NAME, string DESCRIPTIONS, string ADM_UNIT_ID)
        {
            try
            {
                AreaModel DiaBanDieuTra = new AreaModel();
                DiaBanDieuTra.AREA_ID = AREA_ID;
                DiaBanDieuTra.AREA_NAME = AREA_NAME;
                DiaBanDieuTra.DESCRIPTIONS = DESCRIPTIONS;
                DiaBanDieuTra.ADM_UNIT_ID = ADM_UNIT_ID;
                using (WebClient wc = new WebClient())
                {
                    if (ModelState.IsValid)
                    {
                        //DiaBanDieuTra.AdminisUnitID = 241;
                        DiaBanDieuTra.LAST_UPDATED = DateTime.Now;
                        using (MemoryStream ms = new MemoryStream())
                        {
                            Helper.SetHeaderInfo(wc, "EditArea");
                            DataContractJsonSerializer serializerToUpload = new DataContractJsonSerializer(typeof(AreaModel));
                            serializerToUpload.WriteObject(ms, DiaBanDieuTra);
                            wc.Headers["Content-type"] = "application/json; charset=utf-8";
                            wc.UploadData(areaServiceUrl + "EditArea", "POST", ms.ToArray());

                            LogStream.Write(MyUser.UserName,
                            CommonData.AppName,
                            GetFunction(),
                            "Sửa địa bàn điều tra - SuaDiaBanDT", DiaBanDieuTra);

                            return RedirectToAction("IndexArea");
                        }
                    }
                    AdminisUnitModel Admin = new AdminisUnitModel();
                    string AdminDownload = wc.DownloadString(adminUnitServiceUrl + "GetByID" + "/" + DiaBanDieuTra.ADM_UNIT_ID);
                    Admin = JsonConvert.DeserializeObjectAsync<AdminisUnitModel>(AdminDownload).Result;
                    ViewBag.ADM_UNIT = Admin.PROVINCE_NAME + " - " + Admin.DISTRICT_NAME + " - " + Admin.COMMUNE_NAME;
                    return RedirectToAction("IndexHouseHoldPV");
                    //return View(DiaBanDieuTra);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        #endregion

        #region Popup map Area QuyVd Index
        public ActionResult ViewForMapIndex(string Area_code)
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[102] = "active";
            ViewBag.ActiveMenus[107] = "active";
            try
            {
                string strAdminCode = MyUser.AdminCode;
                string strCommuneCode = strAdminCode.Substring(5, 5);

                //phunx1, 20150514: pass url Service len view
                ViewBag.CommuneCode = strCommuneCode;
                ViewBag.Area_code = Area_code;
                return PartialView("ViewForMapPartail");
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        #endregion

        #region popup tao moi dia ban
        public ActionResult CreateAreaPartail()
        {
            return PartialView("ViewForCreatePartail");
        }

        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public ActionResult DiaBanDT(AreaModel DiaBan)
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[102] = "active";
            ViewBag.ActiveMenus[107] = "active";

            try
            {
                using (WebClient wc = new WebClient())
                {
                    if (ModelState.IsValid)
                    {
                        using (MemoryStream ms = new MemoryStream())
                        {
                            Helper.SetHeaderInfo(wc, "AddArea");
                            DataContractJsonSerializer serializerToUpload = new DataContractJsonSerializer(typeof(AreaModel));
                            DiaBan.AREA_CODE = MyUser.AdminCode; //20150702 Quyvd Sử dụng area_code như là admin_code để truyền vào store.
                            DiaBan.ADM_UNIT_ID = MyUser.AdminID;
                            DiaBan.LAST_UPDATED = DateTime.Now;
                            serializerToUpload.WriteObject(ms, DiaBan);
                            wc.Headers["Content-type"] = "application/json; charset=utf-8";
                            wc.UploadData(areaServiceUrl + "AddArea", "POST", ms.ToArray());
                            DiaBan.check = "OKE";

                            LogStream.Write<AreaModel>(MyUser.UserName,
                                CommonData.AppName,
                                GetFunction(),
                                "Thêm mới địa bàn - DiaBanDT", DiaBan);
                        }
                    }
                    return RedirectToAction("IndexArea");
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        #endregion

        #region check area_name
        [HttpPost]
        public JsonResult CheckForAreaName(string AREA_NAME, string AREA_ID)
        {
            try
            {
                if (String.IsNullOrEmpty(AREA_ID))
                    AREA_ID = "khongdungtoi";

                bool result = false;
                using (WebClient wc = new WebClient())
                {
                    Helper.SetHeaderInfo(wc, "CheckForAreaName");
                    string downLoadString = wc.DownloadString(areaServiceUrl + "CheckForAreaName" + "/" + AREA_NAME + "/" + MyUser.AdminID + "/" + AREA_ID);
                    result = Convert.ToBoolean(downLoadString);
                }

                // result: true: chua ton tai, nguoc lai da ton tai
                return Json(result);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        #endregion

        #region Index Area
        public ActionResult IndexArea(string AREA_CODE, string AREA_NAME, int? page)
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[102] = "active";
            ViewBag.ActiveMenus[107] = "active";
            try
            {
                SearchAreaModel search = new SearchAreaModel();
                search.AREA_CODE = AREA_CODE;
                search.AREA_NAME = AREA_NAME;
                search.ADM_UNIT_ID = MyUser.AdminID;
                if (page == null)
                {
                    search.PageIndex = 1;
                }
                else
                    search.PageIndex = page;
                search.PageSize = 10;
                List<AreaModel> areaList = new List<AreaModel>();

                using (WebClient wc = new WebClient())
                {
                    wc.Encoding = Encoding.UTF8;
                    wc.Headers["Content-type"] = "application/json; charset=utf-8";

                    var json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(search);

                    Helper.SetHeaderInfo(wc, "SearchArea");
                    string areaDownload = wc.UploadString(areaServiceUrl + "SearchArea", "POST", json);
                    areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(areaDownload).Result;
                }
                if (page != 0 && areaList.Count == 0)
                {
                    search.PageIndex = 1;
                    using (WebClient wc = new WebClient())
                    {
                        wc.Encoding = Encoding.UTF8;
                        wc.Headers["Content-type"] = "application/json; charset=utf-8";

                        var json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(search);

                        Helper.SetHeaderInfo(wc, "SearchArea");
                        string areaDownload = wc.UploadString(areaServiceUrl + "SearchArea", "POST", json);
                        areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(areaDownload).Result;
                    }
                }
                if (areaList.Count > 0)
                {
                    double x = (double)areaList.ToArray()[0].RECODE_COUNT / 10;
                    double count = Math.Ceiling(x);
                    ViewBag.Count = count;
                }
                else
                    ViewBag.Count = 0;

                ViewBag.Page = search.PageIndex;
                return View(areaList);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        [HttpPost]
        //[AjaxValidateAntiForgeryToken]
        [ValidateAntiForgeryToken]
        public ActionResult IndexArea(SearchAreaModel search)
        {
            try
            {
                if (search == null)
                {
                    return View();
                }
                search.PageIndex = search.PageIndex ?? 1;

                return RedirectToAction("IndexArea", new { search.AREA_CODE, search.AREA_NAME, search.PageIndex });
            }
            catch (Exception ex)
            {
                return Content("Có lỗi xảy ra trong quá trình xử lý dữ liệu: " + ex.Message + ".", "text/html", UTF8Encoding.UTF8);
            }
        }
        #endregion

        #region Export Excel
        public System.Data.DataTable ConvertToDataTable<T>(IList<T> data)
        {
            try
            {
                PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));

                System.Data.DataTable table = new System.Data.DataTable();

                foreach (PropertyDescriptor prop in properties)
                    table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
                if (data != null)
                {
                    foreach (T item in data)
                    {
                        DataRow row = table.NewRow();

                        foreach (PropertyDescriptor prop in properties)
                            row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;

                        table.Rows.Add(row);
                    }
                }
                return table;
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }
        public ActionResult ExportData()
        {
            try
            {
                SearchAreaModel search = new SearchAreaModel();
                search.AREA_CODE = null;
                search.AREA_NAME = null;
                search.ADM_UNIT_ID = MyUser.AdminID;
                search.PageIndex = 1;
                search.PageSize = 999999;
                List<AreaModel> areaList = new List<AreaModel>();

                using (WebClient wc = new WebClient())
                {
                    wc.Encoding = Encoding.UTF8;
                    wc.Headers["Content-type"] = "application/json; charset=utf-8";

                    var json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(search);

                    Helper.SetHeaderInfo(wc, "SearchArea");
                    string areaDownload = wc.UploadString(areaServiceUrl + "SearchArea", "POST", json);
                    areaList = JsonConvert.DeserializeObjectAsync<List<AreaModel>>(areaDownload).Result;
                }

                //tao ra 1 list moi khi muon lay mot so cot nhat dinh
                var list = new List<Tuple<string, string, string, string>>();

                foreach (AreaModel h in areaList)
                {
                    list.Add(Tuple.Create(h.AREA_CODE
                                            , h.AREA_NAME
                                            , h.PROVINCE_NAME + " - " + h.DISTRICT_NAME + " - " + h.COMMUNE_NAME
                                            , h.DESCRIPTIONS
                                         )
                            );
                }
                //tao 1 vung nhớ

                //in ra file excel
                DataTable dt = ConvertToDataTable(list);
                //ExportExcel(dt,"HaNoi");
                string TenDB = MyUser.AdminName;
                using (var excelPackage = new ExcelPackage())
                {
                    excelPackage.Workbook.Properties.Author = "MR. Quy";
                    excelPackage.Workbook.Properties.Title = "Export excel";
                    var sheet = excelPackage.Workbook.Worksheets.Add("Địa Bàn");
                    // output a line for the headers
                    //CreateHeader(sheet);
                    sheet.Name = "DiaBanDT";
                    // all indexes start at 1
                    sheet.Cells["A1"].Value = "Thông tin địa bàn điều tra";
                    sheet.Cells["A1:E1"].Merge = true;
                    sheet.Cells["A1:E1"].Style.Font.Size = 18;
                    sheet.Cells["A1:E1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Cells["A1:E1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    sheet.Cells["A1:E1"].Style.Font.Bold = true;
                    sheet.Cells["A1:E1"].Style.WrapText = true;

                    sheet.Cells["A2"].Value = "(" + TenDB + ")";
                    sheet.Cells["A2:E2"].Merge = true;
                    sheet.Cells["A2:E2"].Style.Font.Size = 10;
                    sheet.Cells["A2:E2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Cells["A2:E2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    sheet.Cells["A2:M2"].Style.Font.Color.SetColor(Color.Red);
                    sheet.Cells["A2:E2"].Style.Font.Bold = true;
                    sheet.Cells["A2:E2"].Style.WrapText = true;

                    using (var rng = sheet.Cells["A4:E4"])
                    {
                        rng.Style.Font.Bold = true;
                        rng.Style.WrapText = true;
                        rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        rng.Style.Font.Size = 12;

                    }

                    sheet.Cells["A4"].Value = "STT";
                    sheet.Cells["B4"].Value = "Mã địa bàn";
                    sheet.Cells["C4"].Value = "Tên địa bàn";
                    sheet.Cells["D4"].Value = "Khu hành chính";
                    sheet.Cells["E4"].Value = "Mô tả";


                    DataTable table = new DataTable();
                    table.Columns.Add("STT", typeof(string));

                    for (int i = 1; i <= dt.Rows.Count; i++)
                    {
                        DataRow row = table.NewRow();
                        row["STT"] = i;
                        table.Rows.Add(row);
                    }
                    sheet.Cells["A5"].LoadFromDataTable(table, true);
                    sheet.Cells["B5"].LoadFromDataTable(dt, true);
                    sheet.DeleteRow(5);

                    for (var j = 0; j < dt.Columns.Count + 1; j++)
                    {
                        for (var i = 0; i <= dt.Rows.Count; i++)
                        {
                            sheet.Cells[i + 4, j + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                            sheet.Cells[i + 4, j + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                            sheet.Cells[i + 4, j + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                            sheet.Cells[i + 4, j + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                        }
                    }

                    sheet.Cells["A4:AN"].AutoFitColumns();
                    sheet.Cells["B4:BN"].AutoFitColumns();
                    sheet.Cells["C4:CN"].AutoFitColumns();
                    sheet.Cells["D4:DN"].AutoFitColumns();
                    sheet.Cells["E4:EN"].AutoFitColumns(30, 70);

                    sheet.Column(3).Style.Numberformat.Format = "$#,##0.00";
                    sheet.Column(2).Style.Numberformat.Format = "$#,##0.00";
                    sheet.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                    sheet.Column(1).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(2).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(3).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(4).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    sheet.Column(5).Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    sheet.Column(5).Style.WrapText = true;

                    using (MemoryStream stream = new MemoryStream())
                    {
                        ZipOutputStream zipstream = new ZipOutputStream(stream);
                        zipstream.SetLevel(3); //0-9, 9 being the highest level of compression
                        //byte[] bytes = null;
                        // loops through the PDFs I need to create
                        var newEntry = new ZipEntry("DanhSachDBDT.xlsx");
                        newEntry.DateTime = DateTime.Now;
                        zipstream.PutNextEntry(newEntry);
                        MemoryStream inStream = new MemoryStream(excelPackage.GetAsByteArray());
                        StreamUtils.Copy(inStream, zipstream, new byte[4096]);
                        inStream.Close();
                        zipstream.CloseEntry();

                        zipstream.IsStreamOwner = false;    // False stops the Close also Closing the underlying stream.
                        zipstream.Close();          // Must finish the ZipOutputStream before using outputMemStream.
                        stream.Position = 0;

                        LogStream.Write<List<AreaModel>>(MyUser.UserName,
                            CommonData.AppName,
                            GetFunction(),
                            "Trích xuất địa bàn ra excell - ExportData", areaList);

                        return File(stream.ToArray(), "application/octet-stream", "DSDB-" + TenDB + ".zip");
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        #endregion

        private string GetFunction()
        {
            return "Quản lý địa bàn điều tra - AreaController";
        }
    }
}
